/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.atm.user.dao;

import com.atm.user.entities.ChuyenKhoan;
import com.atm.user.entities.GuiTien;
import com.atm.user.entities.RutTien;
import com.atm.user.entities.TaiKhoan;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author TuanNguyen
 */
public class ConnectDatabase {
    private String DATABASE_NAME="ATMSystem";
    private String TABLE_CHUYEN_KHOAN="chuyen_khoan";
    private String TABLE_GUI_TIEN="gui_tien";
    private String TABLE_RUT_TIEN="rut_tien";
    private String TABLE_TAI_KHOAN="tai_khoan";
    
    private String password="123456";
    private String username="sa";
    private String url="jdbc:sqlserver://localhost:1433;databaseName="+DATABASE_NAME;
    
    Connection con;

    public ConnectDatabase() {
    }
    
    
    
    public Connection getConnection() throws SQLException {
        Connection con=DriverManager.getConnection(url,username,password);
        return con;
    }
    
    public boolean checkAccount(String accNo){
        boolean check=false;
        Connection con=null;
        String query="SELECT * FROM "+TABLE_TAI_KHOAN+" WHERE so_tk='"+accNo+"'";
        try {
            con=getConnection();
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            if(rs.next())check=true;
            else check=false;
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return check;
       
    }
   
    public TaiKhoan getUser(String accNo){
        Connection con=null;
        TaiKhoan u=new TaiKhoan();
        String query="SELECT * FROM "+TABLE_TAI_KHOAN+" WHERE so_tk='"+accNo+"'";
        try {
            con=getConnection();
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            
            if(rs.next()){
                String PIN=rs.getString(2);
                String chu_tk=rs.getString(3);
                String soCMND=rs.getString(4);
                String dia_chi=rs.getString(5);
                String dienthoai=rs.getString(6);
                String email=rs.getString(7);
                String so_du_tk=rs.getString(8);
                boolean trang_thai=rs.getBoolean(9);
                u=new TaiKhoan(accNo, PIN, chu_tk, soCMND, dia_chi, dienthoai, email, so_du_tk, trang_thai);
            }
            
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return u;
    }
    
    public ArrayList<RutTien> getAllRutTien(String accNo){
        Connection con=null;
        ArrayList<RutTien> list=new ArrayList<RutTien>();
        String query="SELECT * FROM "+TABLE_RUT_TIEN+" WHERE so_tk='"+accNo+"'";
        try {
            con=getConnection();
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            
            while(rs.next()){
                Date date=rs.getDate(2);
                String datetime=rs.getString(2);
                String so_tien_rut=rs.getString(4);
                
                list.add(new RutTien(datetime, so_tien_rut, date));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
    public ArrayList<GuiTien> getAllGuiTien(String accNo){
        Connection con=null;
        ArrayList<GuiTien> list=new ArrayList<GuiTien>();
        String query="SELECT * FROM "+TABLE_GUI_TIEN+" WHERE so_tk='"+accNo+"'";
        try {
            con=getConnection();
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            
            while(rs.next()){
                Date date=rs.getDate(2);
                String datetime=rs.getString(2);
                String so_tien_gui=rs.getString(4);
                
                list.add(new GuiTien(datetime, so_tien_gui, date));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
    public ArrayList<ChuyenKhoan> getAllChuyenTien(String accNo){
        Connection con=null;
        ArrayList<ChuyenKhoan> list=new ArrayList<ChuyenKhoan>();
        String query="SELECT * FROM "+TABLE_CHUYEN_KHOAN+" WHERE so_tk='"+accNo+"'";
        try {
            con=getConnection();
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            
            while(rs.next()){
                Date date=rs.getDate(2);
                String so_tien_gui=rs.getString(5);
                String datetime=rs.getString(2);
                
                list.add(new ChuyenKhoan(datetime,null, so_tien_gui, date));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
    
    public void updateUser(TaiKhoan u){
        Connection con=null;
        String update="UPDATE "+TABLE_TAI_KHOAN+" SET "
                      +"ma_pin='"+u.getPIN()+"'"
                      +", so_du_tk="+u.getSoduTK()
                      +" WHERE so_tk='"+u.getSoTK()+"'";
        try {
            con=getConnection();
            Statement st=con.createStatement();
            st.executeUpdate(update);
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void insertGuiTien(GuiTien gt){
        Connection con=null;
        try {
            con=getConnection();
            PreparedStatement pstmt = con.prepareStatement(
                "INSERT INTO "+TABLE_GUI_TIEN+" ( ngay_gui,so_tk, so_tien_gui) " +
                " VALUES (GETDATE(), ?, ?)");
            pstmt.setString( 2,gt.getSo_tien_gui()); // please use getFir.. instead of GetFir ... 
            pstmt.setString( 1,gt.getSoTk() );
            
            pstmt.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void insertRutTien(RutTien rt){
        Connection con=null;
        try {
            con=getConnection();
            PreparedStatement pstmt = con.prepareStatement(
                "INSERT INTO "+TABLE_RUT_TIEN+" ( ngay_rut,so_tk, so_tien_rut) " +
                " VALUES (GETDATE(), ?, ?)");
            pstmt.setString( 2,rt.getSo_tien_rut()); 
            pstmt.setString( 1,rt.getSoTk() );
            
            pstmt.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public void insertChuyenTien(ChuyenKhoan ct){
        Connection con=null;
        try {
            con=getConnection();
            PreparedStatement pstmt = con.prepareStatement(
                "INSERT INTO "+TABLE_CHUYEN_KHOAN+" ( ngay_ck,so_tk_gui,so_tk_nhan, so_tien_ck) " +
                " VALUES (GETDATE(), ?, ?, ?)");
            pstmt.setString( 3,ct.getSo_tien_gui()); 
            pstmt.setString( 1,ct.getSoTk_gui() );
            pstmt.setString( 2,ct.getSoTk_nhan() );
            
            pstmt.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    
    public float getRutToiDa1Ngay(TaiKhoan u){// tổng số tiền đã rút trong ngày+tien phải <=10 triệu
        ArrayList<RutTien> list=getAllRutTien(u.getSoTK());
        if(list==null || list.size()==0) return 0;
        float tong_tien_rut=0;
        Date current= Calendar.getInstance().getTime();
        for(RutTien i:list){
             System.out.println("date ="+i.getNgay_rut());
            if(i.getNgay_rut().getYear()==current.getYear() &&
               i.getNgay_rut().getMonth()==current.getMonth() &&
               i.getNgay_rut().getDate()==current.getDate() ){
                tong_tien_rut+=Float.parseFloat(i.getSo_tien_rut());
            }
        }
        System.out.println("Tổng số tiền đã rút="+tong_tien_rut);
        return tong_tien_rut;
    }
    public int getSoLanRutToiDa1Ngay(TaiKhoan u){// số lần đã rút trong ngày+1 phải <=5
        ArrayList<RutTien> list=getAllRutTien(u.getSoTK());
        if(list==null || list.size()==0) return 0;
        Date current= Calendar.getInstance().getTime();
        int solanrut=0;
        for(RutTien i:list){
            System.out.println("date ="+i.getNgay_rut());
            if(i.getNgay_rut().getYear()==current.getYear() &&
               i.getNgay_rut().getMonth()==current.getMonth() &&
               i.getNgay_rut().getDate()==current.getDate() ){
                solanrut++;
            }
        }
        System.out.println("Số lần đã rút="+solanrut);
        return solanrut;
    }
    public float getTongTienGui(TaiKhoan u){
        ArrayList<GuiTien> list=getAllGuiTien(u.getSoTK());
        if(list==null || list.size()==0) return 0;
        float tong_tien_gui=0;
        Date current= Calendar.getInstance().getTime();
        for(GuiTien i:list){
             System.out.println("date ="+i.getNgay_gui());
            if(i.getNgay_gui().getYear()==current.getYear() &&
               i.getNgay_gui().getMonth()==current.getMonth() &&
               i.getNgay_gui().getDate()==current.getDate() ){
                tong_tien_gui+=Float.parseFloat(i.getSo_tien_gui());
            }
        }
        System.out.println("Tổng số tiền đã gửi="+tong_tien_gui);
        return tong_tien_gui;
    }
    public int getSoLanGui(TaiKhoan u){
        ArrayList<GuiTien> list=getAllGuiTien(u.getSoTK());
        if(list==null || list.size()==0) return 0;
        Date current= Calendar.getInstance().getTime();
        int solangui=0;
        for(GuiTien i:list){
            System.out.println("date ="+i.getNgay_gui());
            if(i.getNgay_gui().getYear()==current.getYear() &&
               i.getNgay_gui().getMonth()==current.getMonth() &&
               i.getNgay_gui().getDate()==current.getDate() ){
                solangui++;
            }
        }
        System.out.println("Số lần đã gửi="+solangui);
        return solangui;
    }
    
    public ArrayList<GuiTien> getListTienGuiTrongNgay(TaiKhoan u){
        ArrayList<GuiTien> list=getAllGuiTien(u.getSoTK());
        ArrayList<GuiTien> listTrongNgay=new ArrayList<GuiTien>();
        if(list==null || list.size()==0) return listTrongNgay;
        Date current= Calendar.getInstance().getTime();
        for(GuiTien i:list){
             System.out.println("date ="+i.getNgay_gui());
            if(i.getNgay_gui().getYear()==current.getYear() &&
               i.getNgay_gui().getMonth()==current.getMonth() &&
               i.getNgay_gui().getDate()==current.getDate() ){
                listTrongNgay.add(i);
            }
        }
        return listTrongNgay;
    }
     public ArrayList<RutTien> getListTienRutTrongNgay(TaiKhoan u){
        ArrayList<RutTien> list=getAllRutTien(u.getSoTK());
        ArrayList<RutTien> listTrongNgay=new ArrayList<RutTien>();
        if(list==null || list.size()==0) return listTrongNgay;
        Date current= Calendar.getInstance().getTime();
        for(RutTien i:list){
             System.out.println("date ="+i.getNgay_rut());
            if(i.getNgay_rut().getYear()==current.getYear() &&
               i.getNgay_rut().getMonth()==current.getMonth() &&
               i.getNgay_rut().getDate()==current.getDate() ){
                listTrongNgay.add(i);
            }
        }
        return listTrongNgay;
    }
    public ArrayList<ChuyenKhoan> getListTienChuyenTrongNgay(TaiKhoan u){
        ArrayList<ChuyenKhoan> list=getAllChuyenTien(u.getSoTK());
        ArrayList<ChuyenKhoan> listTrongNgay=new ArrayList<ChuyenKhoan>();
        if(list==null || list.size()==0) return listTrongNgay;
        Date current= Calendar.getInstance().getTime();
        for(ChuyenKhoan i:list){
             System.out.println("date ="+i.getNgay_gui());
            if(i.getNgay_gui().getYear()==current.getYear() &&
               i.getNgay_gui().getMonth()==current.getMonth() &&
               i.getNgay_gui().getDate()==current.getDate() ){
                listTrongNgay.add(i);
            }
        }
        return listTrongNgay;
    }
    
}
